package com.tongzhou.tongzhou.account.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.tongzhou.db.DBUtil;
import com.tongzhou.db.exception.DBException;
import com.tongzhou.tongzhou.account.dao.IAccountDAO;
import com.tongzhou.tongzhou.account.model.Account;
import com.tongzhou.tongzhou.account.model.Client;
import com.tongzhou.tongzhou.account.model.SearchAccountCondition;
import com.tongzhou.tongzhou.account.model.SearchClientCondition;
import com.tongzhou.tongzhou.initInfo.model.QuotType;
import com.tongzhou.tongzhou.initInfo.model.Region;
import com.tongzhou.tongzhou.quot.model.Paper;
import com.tongzhou.tongzhou.quot.model.PaperType;
import com.tongzhou.util.Util;

public class AccountDAO implements IAccountDAO{
	
	@Override
	public int addAccount(Account account) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("insert into account(company_name,company_num,rid,high_risk,offer_type,");
		sql.append("offer_call,active,create_time,email,fax,address,remark) ");
		sql.append("values('"+Util.changeToObj(account.getCompany_name())+"',");
		sql.append("'"+Util.changeToObj(account.getCompany_num())+"',");
		sql.append("'"+account.getRid()+"',");
		sql.append("'"+account.getHigh_risk()+"',");
		sql.append("'"+account.getOffer_type()+"',");
		sql.append("'"+Util.changeToObj(account.getOffer_call())+"',");
		sql.append("'"+Util.changeToObj(account.getActive())+"',");
		sql.append("now(),");
		sql.append("'"+Util.changeToObj(account.getEmail())+"',");
		sql.append("'"+Util.changeToObj(account.getFax())+"',");
		sql.append("'"+Util.changeToObj(account.getAddress())+"',");
		sql.append("'"+Util.changeToObj(account.getRemark())+"');");
		int result=DBUtil.update(sql.toString());
		return result;
	}

	@Override
	public List<Account> listAccount(SearchAccountCondition condition)
			throws DBException {
		List<Account> result=null;
		
		//查询条件SQL
		//查询参数
		StringBuffer sqlCondition = new StringBuffer() ;
		List<String> paramList = new ArrayList<String>() ;
		
		if(condition.getCompany_name() != null && !condition.getCompany_name().equals("")){
			sqlCondition.append(" and a.company_name like ?");
			paramList.add("%"+condition.getCompany_name()+"%");
		}
		if(condition.getCompany_num() != null && !condition.getCompany_num().equals("")){
			sqlCondition.append(" and a.company_num like ?") ;
			paramList.add("%"+condition.getCompany_num()+"%") ;
		}
		
		//取得满足条件的总记录数和总页数
		StringBuffer sql = new StringBuffer() ;
		sql.append("select count(a.id) ");
		sql.append(" from account a left join region r on a.rid=r.id ");
		sql.append(" left join quot_type q on a.rid=q.id ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		
		
		//总记录数
		Map<String,String> dataMap=new HashMap<String,String>();
		long total = DBUtil.getCount(sql.toString(),paramList.toArray()) ;
		//总页数
		long totalPage = Util.getTotalPage(total, condition.getPageSize());
		
		condition.setTotal(total);
		condition.setTotalPage(totalPage);
		
		//查询某页的列表数据
		sql.delete(0, sql.length());
		sql.append("select  a.id,a.company_name,a.company_num,a.rid,");
		sql.append("a.high_risk,a.offer_type,a.offer_call,a.active,");
		sql.append("a.create_time,a.email,a.fax,a.address,a.remark,");
		sql.append("r.region_name,q.quot_type_name ");
		sql.append(" from account a left join region r on a.rid=r.id ");
		sql.append(" left join quot_type q on a.rid=q.id ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		sql.append(" order by "+condition.getOrderBy()+" "+condition.getOrderByType()) ;
		result = DBUtil.query(sql.toString(), new BeanListHandler<Account>(Account.class), paramList.toArray(), Util.getStartPos(condition.getCurrentPage(), condition.getPageSize()), condition.getPageSize()) ;
		return result;
	}
	
	@Override
	public List<Account> checkLoginInfo(Account account) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select  a.id,a.company_name,a.company_num,a.rid,");
		sql.append("a.high_risk,a.offer_type,a.offer_call,a.active,");
		sql.append("a.create_time,a.email,a.fax,a.address,a.remark ");
		sql.append(" from account a ");
		sql.append(" where 1=1 ");
		if(account.getCompany_name()!=null){
			sql.append(" and a.company_name='"+account.getCompany_name()+"'");
		}
		if(account.getCompany_num()!=null){
			sql.append(" and a.company_num='"+account.getCompany_num()+"'");
		}
		List<Account> result=DBUtil.query(sql.toString(),new BeanListHandler<Account>(Account.class));
		return result;
	}

	@Override
	public void deleteAccount(int id) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("delete from account where id='"+id+"'");
		DBUtil.update(sql.toString());
	}

	@Override
	public Account getAccountById(int id) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select  a.id,a.company_name,a.company_num,a.rid,");
		sql.append("a.high_risk,a.offer_type,a.offer_call,a.active,");
		sql.append("a.create_time,a.email,a.fax,a.address,a.remark,");
		sql.append("r.region_name,q.quot_type_name ");
		sql.append(" from account a left join region r on a.rid=r.id ");
		sql.append(" left join quot_type q on a.rid=q.id ");
		sql.append(" where a.id='"+id+"'");
		Account account=DBUtil.query(sql.toString(), new BeanHandler<Account>(Account.class));
		return account;
	}

	@Override
	public void updateAccount(Account account) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("update account set ");
		sql.append("company_name='"+account.getCompany_name()+"',");
		sql.append("company_num='"+account.getCompany_num()+"',");
		sql.append("rid='"+account.getRid()+"',");
		sql.append("high_risk='"+account.getHigh_risk()+"',");
		sql.append("offer_type='"+account.getOffer_type()+"',");
		sql.append("offer_call='"+account.getOffer_call()+"',");
		sql.append("active='"+account.getActive()+"',");
		sql.append("email='"+account.getEmail()+"',");
		sql.append("fax='"+account.getFax()+"',");
		sql.append("address='"+account.getAddress()+"',");
		sql.append("remark='"+account.getRemark()+"' ");
		sql.append("where id='"+account.getId()+"'");
		DBUtil.update(sql.toString());
	}
	
	@Override
	public List<Account> checkAccountByCode(Account account) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select  a.id ");
		sql.append(" from account a ");
		sql.append(" where 1=1 ");
		if(account.getId()!=0){
			sql.append(" and a.id='"+account.getId()+"'");
		}
		sql.append(" and a.company_num='"+account.getCompany_num()+"'");
		List<Account> result=DBUtil.query(sql.toString(),new BeanListHandler<Account>(Account.class));
		return result;
	}

	@Override
	public int addClient(Client client) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("insert into client(company_name,contact_call,active,create_time)  ");
		sql.append(" values('"+client.getCompany_name()+"','"+client.getContact_call()+"','N',now());");
		int result=DBUtil.update(sql.toString());
		return result;
	}

	@Override
	public List<Client> listClient(SearchClientCondition condition)
			throws DBException {
		List<Client> result=null;
		
		//查询条件SQL
		//查询参数
		StringBuffer sqlCondition = new StringBuffer() ;
		List<String> paramList = new ArrayList<String>() ;
		
		if(condition.getActive() != null && !condition.getActive().equals("")){
			sqlCondition.append(" and active = ?");
			paramList.add(condition.getActive());
		}
		
		//取得满足条件的总记录数和总页数
		StringBuffer sql = new StringBuffer() ;
		sql.append("select count(id) ");
		sql.append(" from client a ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		
		
		//总记录数
		Map<String,String> dataMap=new HashMap<String,String>();
		long total = DBUtil.getCount(sql.toString(),paramList.toArray()) ;
		//总页数
		long totalPage = Util.getTotalPage(total, condition.getPageSize());
		
		condition.setTotal(total);
		condition.setTotalPage(totalPage);
		
		//查询某页的列表数据
		sql.delete(0, sql.length());
		sql.append("select id,company_name,contact_call,active,date_format(create_time,'%Y-%m-%d %h:%i:%s') create_time ");
		sql.append(" from client ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		sql.append(" order by "+condition.getOrderBy()+" "+condition.getOrderByType()) ;
		result = DBUtil.query(sql.toString(), new BeanListHandler<Client>(Client.class), paramList.toArray(), Util.getStartPos(condition.getCurrentPage(), condition.getPageSize()), condition.getPageSize()) ;
		return result;
	}

	@Override
	public void updateClient(Client client) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("update client  set ");
		sql.append("active='"+client.getActive()+"' ");
		sql.append("where id='"+client.getId()+"'");
		DBUtil.update(sql.toString());
	}

	@Override
	public void deleteClient(int id) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("delete from client where id='"+id+"'");
		DBUtil.update(sql.toString());
	}

	@Override
	public List<QuotType> getQuotTypes() throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select  id,quot_type_name ");
		sql.append(" from quot_type ");
		List<QuotType> result=DBUtil.query(sql.toString(),new BeanListHandler<QuotType>(QuotType.class));
		return result;
	}

	@Override
	public List<Region> getRegions() throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select  id,region_name ");
		sql.append(" from region ");
		List<Region> result=DBUtil.query(sql.toString(),new BeanListHandler<Region>(Region.class));
		return result;
	}

	@Override
	public void deleteAccountQuot(int accountId) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("delete from paper where paper_type_id in (select id from paper_type where account_id='"+accountId+"')");
		DBUtil.update(sql.toString());
		
		sql.delete(0, sql.length());
		sql.append("delete from paper_type where account_id='"+accountId+"'");
		DBUtil.update(sql.toString());
	}

	@Override
	public List<Paper> getPaperList(String paperTypeId) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select id,paper_name,price,keng,paper_type_id from paper ");
		sql.append(" where 1=1 ");
		sql.append(" and paper_type_id='"+paperTypeId+"'");
		List<Paper> result=DBUtil.query(sql.toString(),new BeanListHandler<Paper>(Paper.class));
		return result;
	}

	@Override
	public List<PaperType> getPaperTypeList(int account_id) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select id,paper_type_name,account_id from paper_type ");
		sql.append(" where 1=1 ");
		sql.append(" and account_id='"+account_id+"'");
		sql.append(" order by id asc ");
		List<PaperType> result=DBUtil.query(sql.toString(),new BeanListHandler<PaperType>(PaperType.class));
		return result;
	}
	
	@Override
	public Account getAccountByNum(String companyNum) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select  a.id,a.company_name,a.company_num,a.rid,");
		sql.append("a.high_risk,a.offer_type,a.offer_call,a.active,");
		sql.append("a.create_time,a.email,a.fax,a.address,a.remark ");
		sql.append(" from account a ");
		sql.append(" where a.company_num='"+companyNum+"'");
		Account account=DBUtil.query(sql.toString(), new BeanHandler<Account>(Account.class));
		return account;
	}
}
